# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
from models import Comm, Employee, Supplier, Student, Sc, Game, Prob1
from utils import generate_pretty_table
from constants import DATABASE

from sqlalchemy import MetaData, inspect
from sqlalchemy.orm import Session, sessionmaker, aliased
from sqlalchemy import create_engine
from sqlalchemy import func, cast, DECIMAL, column
from sqlalchemy import case, literal_column
from datetime import datetime

    
#1.需要查询进货价格（c_inprice）大于20的所有商品
def q1(session):
    model_ = Comm
    res = session.query(model_).all()
    generate_pretty_table(res, model_class=model_)
    

#4.输出单件商品的利润（进货价格（c_inprice），售出价格（c_outprice））
#5.求服装类型的每件商品的利润
#6.所有商品全部卖出后的利润是多少？(新增利润列即可)
#7.查询产品信息表的产品和产地
def q2(session):
    model_ = Comm
    res = session.query(
        model_.c_id, 
        model_.c_name,
        (func.round(model_.c_outprice - model_.c_inprice, 2).label('利润'))).all()
    pt = generate_pretty_table(res, ['c_id', 'c_name', '利润'])
    
#8.想在表中查询已经有几种货物种类（c_type）
def q3(session):
    model_ = Comm
    res = session.query(model_)
    pt = generate_pretty_table(res,model_class=model_)
    
##13）我要查询姓名中含有‘佳’字的员工编号、身份证号码，姓名及部门。(考察模糊条件查询)
# select 员工编号,姓名,身份证号码,部门
# from  员工
# where 姓名 like '%佳%'
def q4(session):
    model_ = Employee
    res = session.query(model_).filter(Employee.name.like('%'+'佳'+'%')).limit(10).all()
    pt = generate_pretty_table(res, model_class=model_)
    
# select 姓名,year(now())-year(出生日期) as 年龄,部门,目前薪资
# from 员工
# where 部门='生产制造部'
def q5(session):
    model_ = Employee
    res = session.query(
        model_.name, 
        (
              cast((func.to_days(func.now()) - func.to_days(model_.birth_date))/365.25, DECIMAL(10,0))
        ),
        model_.department, model_.cur_salary
    )
    pt = generate_pretty_table(res, ['name', 'age', 'department', 'cur_salary'])

# select *
# from 选课
# order  by 成绩 desc 
def q6(session):
    model_ = Sc  
    res = session.query(model_).order_by(model_.score.desc()).all()
    pt = generate_pretty_table(res, model_class=Sc)
    
#commodity表中进货价格在20-200之间的数据，进价降序排序
#如果进货价格一致，则按照卖出数量进行升序排序
# select *
# from commodity 
# where c_inprice  between 20 and 200
# order  by  c_inprice desc,c_num
def q7(session):
    model_ = Comm
    res = session.query(model_).filter(model_.c_inprice.between(20, 200)).order_by(model_.c_inprice.desc()).order_by(model_.c_num).limit(10)
    pt = generate_pretty_table(res, model_class=model_)
    
#员工表各部门总薪资，平均薪资，总人数，最高薪资，最低薪资
# select 部门, sum(目前薪资) as 总薪资,round(avg(目前薪资),2) as  平均薪资,count(*) as 总人数,
# max(目前薪资) as 最高薪资,min(目前薪资) as 最低薪资
# from  员工
# group  by 部门
def q8(session):
    model_ = Employee
    res = session.query(
        model_.department.label('部门'),
        func.sum(model_.cur_salary).label('总薪资'),
        func.cast(func.avg(model_.cur_salary), DECIMAL(10, 2)).label('平均薪资'),
        func.count().label('总人数'),
        func.min(model_.cur_salary).label('最低薪资'),
        func.max(model_.cur_salary).label('最高薪资')
    ).group_by(model_.department).having(column('部门')=='业务部')
    pt = generate_pretty_table(res, ['部门', '总薪资', '平均薪资', '总人数', '最低薪资', '最高薪资'])
    
# select *from 员工
# where year(now()) - year(出生日期)=(
#                 select max(year(now()) - year(出生日期)) from 员工)
def q9(session):
    sq_ = session.query(func.max(func.year(func.now())-func.year(Employee.birth_date)).label('oldest')).subquery()
    res = session.query(Employee).filter(func.year(func.now())-func.year(Employee.birth_date) == sq_)
    pt = generate_pretty_table(res, model_class=Employee)
    


#1.查询员工表中有哪些部门（2种写法）
def q10(session):
    res = session.query(Employee.department).distinct()
    print('方法一：\n')
    generate_pretty_table(res, ['department'])
    print('方法二:\n')
    res = session.query(Employee.department).group_by(Employee.department).all()
    generate_pretty_table(res, ['department'])
#2.查询行政区在武汉、天津、北京的信息（northwind-商品供应）
def q11(session):
    res = session.query(Supplier).filter(Supplier.district.in_(['武汉', '天津', '北京'])).all()
    pt = generate_pretty_table(res, model_class=Supplier)
#3.统计员工表中每个部门有个多少个员工（员工表）
def q12(session):
    res = session.query(Employee.department, func.count('*').label('总人数')).group_by(Employee.department).all()
    pt = generate_pretty_table(res, ['部门', '总人数'])
#5.查询产品名称不是苹果汁、大甲蟹、龙虾的其他商品（2中写法-northwind-产品资料）
#7.查询每种产品名称有多少条记录（northwind-产品资料）
#8.查询产品资料表有多少库存量（northwind-产品资料，不用考虑已购量）
#9.求出每种产品的种销售额（northwind-产品资料）
#10.求库存量在20-50的数据，以库存量进行降序排序，库存量一致，单价降序拍序，展示第二页的数据（每页展示5条数据）
#11.查询比生产制造部平均工资还要高的其他部门员工的工资情况
def q13(session):
    sq_ = session.query(func.avg(Employee.cur_salary)).filter(Employee.department == '生产制造部').subquery()
    res = session.query(Employee.name, Employee.cur_salary).filter(Employee.cur_salary > sq_)
    pt = generate_pretty_table(res, ['部门', '平均薪水'])
    
# select 姓名,部门,if(性别=-1,'女','男') as 性别1,性别
# from 员工
def q14(session):
    res =  session.query(Employee.name,Employee.department,
                          case(
                              (Employee.gender == -1, '女'),
                              (Employee.gender == 'false', '女'),
                              (Employee.gender == 0, '男'),
                              (Employee.gender == 'true', '男'),
                              else_ = None
                        ).label('性别1'), Employee.gender)
    pt = generate_pretty_table(res, ['姓名', '部门', '性别1', '性别'])

#1.对选课表中学生成绩做评价（techdb-选课表）
#<70:不及格 70-80：及格 80-90：良好 >90:优秀
def q15(session):
    res = session.query(
            Sc.s_id,
            case(
                (Sc.score >= 90, '优秀'),
                (Sc.score >= 80, '良好'),
                (Sc.score >= 70, '及格'),
                else_= '不及格'
            ).label('评价')
        )
    pt = generate_pretty_table(res, ['id', 'rank'])
#2.题目--数据集间jinjie库的game表
#关于game表的内容如下：
# 2005-05-09 胜
# 2005-05-09 胜
# 2005-05-09 负
# 2005-05-09 负
# 2005-05-10 胜
# 2005-05-10 负
# 2005-05-10 负
#要求根据上表统计出如下信息：
# 日期 场次 胜 负
# 2005-05-09 4 2 2
# 2005-05-10 3 1 2
def q16(session):
    res = session.query(
            Game.rq,
            func.sum(
                case(
                    (Game.result == '胜', 1),
                    else_ = 0
                    )
            ).label('win'),
            func.sum(
                case(
                    (Game.result == '负', 1),
                    else_ = 0
                    )
            ).label('lose')
        ).group_by(Game.rq).all()
    pt = generate_pretty_table(res, ['rq', 'win', 'lose'])

# select  b.*
# from  选课 a right join 学生 b 
# on a.学生号=b.学生号
# where  a.学生号 is null
def q17(session):
    Student2 = aliased(Student, name='Student2')
    res = session.query(Student, Student2).outerjoin(Student2, Student2.id == Student.id).limit(5).all()
    pt = generate_pretty_table(res)
    
#代入
# select  * from  商品表1
# where  单价>(select  min(单价) from  商品表1
#                 where  分类名='洗衣机') and  分类名<>'洗衣机'
def q18(session):
    pb = aliased(Prob1, name='Student2')
    sq_ = session.query(func.min(pb.price)).subquery()
    res = session.query(pb).filter(pb.price > sq_).all()
    pt = generate_pretty_table(res)
    
# select  * from  商品表1
# where  单价>any(select  单价 from  商品表1
#                 where  分类名='洗衣机') and  分类名<>'洗衣机'
def q19(session):
    pb = aliased(Prob1, name='pb')
    sq_ = session.query(pb.price).filter(pb.category == '洗衣机' and pb.category).subquery()
    res = session.query(pb).filter(pb.price > any_(sq_) and pb.category != '洗衣机')
    pt = generate_pretty_table(res)

if __name__ == '__main__':
    engine = create_engine(f"mysql+pymysql://root:19930405@localhost:3306/{DATABASE}", echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()
    q19(session)
    

    
    


    

    

    
    

    
    
    

    
    

    
    
    
    


